CREATE TABLE  "ASR_PRIORITY" 
   (	"ID" NUMBER, 
	"CODE" VARCHAR2(50), 
	"DESCRIPTION" VARCHAR2(500), 
	 CONSTRAINT "ASR_PRIORITY_PK" PRIMARY KEY ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_RESULT_TYPE" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CODE" VARCHAR2(50) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(500), 
	 CONSTRAINT "ASR_RESULT_TYPE_PK" PRIMARY KEY ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_TASK_TYPE" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CODE" VARCHAR2(50) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(500), 
	 CONSTRAINT "ASR_TASK_TYPE_PK" PRIMARY KEY ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_TASK" 
   (	"ID" NUMBER, 
	"UNIQUE_ID" VARCHAR2(50) NOT NULL ENABLE, 
	"NAME" VARCHAR2(50) NOT NULL ENABLE, 
	"MEDIA_LOCATION" VARCHAR2(2000) NOT NULL ENABLE, 
	"TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PRIORITY_ID" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "ASR_TASK_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "ASR_TASK_ASR_PRIORITY_FK1" FOREIGN KEY ("PRIORITY_ID")
	  REFERENCES  "ASR_PRIORITY" ("ID") ENABLE, 
	 CONSTRAINT "ASR_TASK_ASR_TASK_TYPE_FK1" FOREIGN KEY ("TYPE_ID")
	  REFERENCES  "ASR_TASK_TYPE" ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_RESULT" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CONTENT" CLOB, 
	"RESULT_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"TASK_ID" NUMBER(*,0), 
	 CONSTRAINT "ASR_RESULT_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "ASR_RESULT_ASR_TASK_FK1" FOREIGN KEY ("TASK_ID")
	  REFERENCES  "ASR_TASK" ("ID") ENABLE, 
	 CONSTRAINT "ASR_RESULT_ASR_RESULT_TYP_FK1" FOREIGN KEY ("RESULT_TYPE_ID")
	  REFERENCES  "ASR_RESULT_TYPE" ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_STATE" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CODE" VARCHAR2(50) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(500), 
	 CONSTRAINT "ASR_STATE_PK" PRIMARY KEY ("ID") ENABLE
   )
/
CREATE TABLE  "ASR_STATE_HISTORY" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"STATE_ID" NUMBER NOT NULL ENABLE, 
	"TASK_ID" NUMBER NOT NULL ENABLE, 
	"ADATE" TIMESTAMP (6) NOT NULL ENABLE, 
	 CONSTRAINT "ASR_STATE_HISTORY_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "ASR_STATE_HISTORY_ASR_TAS_FK1" FOREIGN KEY ("TASK_ID")
	  REFERENCES  "ASR_TASK" ("ID") ENABLE, 
	 CONSTRAINT "ASR_STATE_HISTORY_ASR_STA_FK1" FOREIGN KEY ("STATE_ID")
	  REFERENCES  "ASR_STATE" ("ID") ENABLE
   )
/

Rem No function found to generate DDL.
CREATE UNIQUE INDEX  "ASR_PRIORITY_PK" ON  "ASR_PRIORITY" ("ID")
/
CREATE UNIQUE INDEX  "ASR_RESULT_PK" ON  "ASR_RESULT" ("ID")
/
CREATE UNIQUE INDEX  "ASR_RESULT_TYPE_PK" ON  "ASR_RESULT_TYPE" ("ID")
/
CREATE UNIQUE INDEX  "ASR_STATE_HISTORY_PK" ON  "ASR_STATE_HISTORY" ("ID")
/
CREATE UNIQUE INDEX  "ASR_STATE_PK" ON  "ASR_STATE" ("ID")
/
CREATE UNIQUE INDEX  "ASR_TASK_PK" ON  "ASR_TASK" ("ID")
/
CREATE UNIQUE INDEX  "ASR_TASK_TYPE_PK" ON  "ASR_TASK_TYPE" ("ID")
/
CREATE UNIQUE INDEX  "SYS_IL0000013650C00002$$" ON  "ASR_RESULT" (
/

CREATE OR REPLACE PACKAGE  "RESULTS" AS
-- types declaration
TYPE T_CURSOR IS REF CURSOR;

-- stored procedures declaration
PROCEDURE InsertResult
(
    taskId IN ASR_RESULT.TASK_ID%TYPE,
    typeCode IN ASR_RESULT_TYPE.CODE%TYPE,
    content IN ASR_RESULT.CONTENT%TYPE,
    id OUT INT
);

PROCEDURE GetResultsByTaskId
(
    taskId IN ASR_RESULT.TASK_ID%TYPE,
    resultsCursor OUT T_CURSOR 
);

END RESULTS;
/
CREATE OR REPLACE PACKAGE BODY  "RESULTS" AS

/*
 * Description : Inserts a new task result into database
 * Author : Lukasz Laszko
 * Params : taskId - existing task id
 *          typeCode  - result type code
 *          content   - result textual content
 * Return values: id  - >0  - all ok, result id
 *                    - -1  - invalid task id
 *                    - -2  - invalid type code
 * History : 2008-03-21 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertResult
(
    taskId IN ASR_RESULT.TASK_ID%TYPE,
    typeCode IN ASR_RESULT_TYPE.CODE%TYPE,
    content IN ASR_RESULT.CONTENT%TYPE,
    id OUT INT
) 
AS
    taskExists INT;
    typeExists INT;
    
    typeId ASR_RESULT_TYPE.ID%TYPE;
BEGIN
    -- check if task exists
    SELECT COUNT(*) INTO taskExists FROM ASR_TASK
    WHERE (ID = taskId);
    IF taskExists = 0 THEN
      id := -2;
      RETURN;
    END IF;
  
    -- check if type code is valid
    SELECT COUNT(*) INTO typeExists FROM ASR_RESULT_TYPE
    WHERE (CODE = typeCode) AND (ROWNUM <= 1);
    IF typeExists = 0 THEN
      id := -2;
      RETURN;
    ELSE
      SELECT ID INTO typeId FROM ASR_RESULT_TYPE
      WHERE (CODE = typeCode) AND (ROWNUM <= 1);  
    END IF;    
    
    -- insert new result
    INSERT INTO ASR_RESULT(TASK_ID, RESULT_TYPE_ID, CONTENT)
                    VALUES(taskId, typeId, content)
                    RETURNING ID
                    INTO  id;
END InsertResult;

/*
 * Description : Inserts a new task result into database
 * Author : Lukasz Laszko
 * Params : taskId - existing task id
 * History : 2008-03-21 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetResultsByTaskId
(
    taskId IN ASR_RESULT.TASK_ID%TYPE,
    resultsCursor OUT T_CURSOR 
)
AS
BEGIN
    OPEN  resultsCursor FOR
    SELECT  ASR_RESULT.ID AS ID,
            ASR_RESULT.TASK_ID AS TASK_ID,
            ASR_RESULT.CONTENT AS CONTENT,
            ASR_RESULT_TYPE.CODE AS TYPE_CODE
    FROM    ASR_RESULT INNER JOIN ASR_RESULT_TYPE ON ASR_RESULT.RESULT_TYPE_ID = ASR_RESULT_TYPE.ID
    WHERE   ASR_RESULT.TASK_ID = taskId;
END;

END RESULTS;
/
 CREATE OR REPLACE PACKAGE  "TASKS" AS
-- types declaration
TYPE T_CURSOR IS REF CURSOR;

-- stored procedures declaration
PROCEDURE InsertTask
(
    uniqueId IN ASR_TASK.UNIQUE_ID%TYPE,
    name IN ASR_TASK.NAME%TYPE,
    mediaLocation IN ASR_TASK.MEDIA_LOCATION%TYPE,
    typeCode IN ASR_TASK_TYPE.CODE%TYPE,
    priorityCode IN ASR_PRIORITY.CODE%TYPE,
    id OUT INT
);

PROCEDURE GetTasks
(
    tasksCursor OUT T_CURSOR
);

PROCEDURE GetTaskByUniqueId
(
    taskUniqueId IN ASR_TASK.UNIQUE_ID%TYPE,
    tasksCursor OUT T_CURSOR
);

PROCEDURE GetTasksByState
(
    stateCode IN ASR_STATE.CODE%TYPE,
    tasksCursor OUT T_CURSOR
);

PROCEDURE SetTaskState
(
    taskId IN ASR_TASK.ID%TYPE,
    stateCode IN ASR_STATE.CODE%TYPE,
    resultCode OUT INT
);

PROCEDURE GetTaskHistory
(
    taskId IN ASR_TASK.ID%TYPE,
    taskHistoryCursor OUT T_CURSOR
);

END TASKS;
/
CREATE OR REPLACE PACKAGE BODY  "TASKS" AS

/*
 * Description : Inserts a new task into database
 * Author : Lukasz Laszko
 * Params : uniqueId - unique identifier of task (GUID)
 *          name  - human readable task name
 *          mediaLocation  - location of temprary media file
 *          typeCode - task type code (from ASR_TASK_TYPE table)
 *          priorityCode - task priority code  (from ASR_PRIRITY)
 * Return values: id  - >0  - all ok, task id
 *                    - -1  - invalid type code
 *                    - -2  - invalid priority code
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertTask
(
    uniqueId IN ASR_TASK.UNIQUE_ID%TYPE,
    name IN ASR_TASK.NAME%TYPE,
    mediaLocation IN ASR_TASK.MEDIA_LOCATION%TYPE,
    typeCode IN ASR_TASK_TYPE.CODE%TYPE,
    priorityCode IN ASR_PRIORITY.CODE%TYPE,
    id OUT INT
) 
AS
    typeExists INT;
    priorityExists INT;

    typeId ASR_TASK_TYPE.ID%TYPE;
    priorityId ASR_PRIORITY.ID%TYPE;      
BEGIN
    -- check if type code is valid
    SELECT COUNT(*) INTO typeExists FROM ASR_TASK_TYPE
    WHERE (CODE = typeCode) AND (ROWNUM <= 1);
    IF typeExists = 0 THEN
      id := -1;
      RETURN;
    ELSE
      SELECT ID INTO typeId FROM ASR_TASK_TYPE
      WHERE (CODE = typeCode) AND (ROWNUM <= 1);  
    END IF;
    
    -- check if priority code is valid
    SELECT COUNT(*) INTO priorityExists FROM ASR_PRIORITY
    WHERE (CODE = priorityCode) AND (ROWNUM <= 1);
    IF priorityExists = 0 THEN
      id := -2;
      RETURN;
    ELSE
      SELECT ID INTO priorityId FROM ASR_PRIORITY
      WHERE (CODE = priorityCode) AND (ROWNUM <= 1);
    END IF;
    
    -- insert task
    INSERT INTO ASR_TASK(UNIQUE_ID, NAME, MEDIA_LOCATION, TYPE_ID, PRIORITY_ID)
                  VALUES(uniqueId, name, mediaLocation, typeId, priorityId)
                  RETURNING ID
                  INTO  id;
END InsertTask;

/*
 * Description : Gets all tasks from database
 * Author : Lukasz Laszko
 * Return values: tasksCursor - reference cursor to task records 
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetTasks
(
    tasksCursor OUT T_CURSOR
)
AS
BEGIN
  OPEN  tasksCursor FOR
  SELECT  ASR_TASK.ID AS ID,
          ASR_TASK.UNIQUE_ID AS UNIQUE_ID,
          ASR_TASK.NAME AS NAME,
          ASR_TASK.MEDIA_LOCATION AS MEDIA_LOCATION,
          ASR_TASK_TYPE.CODE AS TYPE_CODE,
          ASR_PRIORITY.CODE AS PRIORITY_CODE,
          (SELECT ASR_STATE.CODE 
           FROM   ASR_STATE INNER JOIN ASR_STATE_HISTORY ON ASR_STATE.ID = ASR_STATE_HISTORY.STATE_ID
           WHERE  (ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID) AND (ROWNUM = 1) AND 
                  (ASR_STATE_HISTORY.ADATE = (SELECT MAX(ADATE) 
                                              FROM ASR_STATE_HISTORY 
                                              WHERE ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID))) AS STATE_CODE 
  FROM ASR_TASK LEFT JOIN ASR_TASK_TYPE ON ASR_TASK.TYPE_ID = ASR_TASK_TYPE.ID
                LEFT JOIN ASR_PRIORITY ON ASR_TASK.PRIORITY_ID = ASR_PRIORITY.ID;
END GetTasks;

/*
 * Description : Gets task of given unique id
 * Author : Lukasz Laszko
 * Params : taskUniqueId  - task unique id
 * Return values: tasksCursor - reference cursor to task record 
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetTaskByUniqueId
(
    taskUniqueId IN ASR_TASK.UNIQUE_ID%TYPE,
    tasksCursor OUT T_CURSOR
)
AS
BEGIN
  OPEN  tasksCursor FOR
  SELECT  ASR_TASK.ID AS ID,
          ASR_TASK.UNIQUE_ID AS UNIQUE_ID,
          ASR_TASK.NAME AS NAME,
          ASR_TASK.MEDIA_LOCATION AS MEDIA_LOCATION,
          ASR_TASK_TYPE.CODE AS TYPE_CODE,
          ASR_PRIORITY.CODE AS PRIORITY_CODE,
          (SELECT ASR_STATE.CODE 
           FROM   ASR_STATE INNER JOIN ASR_STATE_HISTORY ON ASR_STATE.ID = ASR_STATE_HISTORY.STATE_ID
           WHERE  (ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID) AND (ROWNUM = 1) AND 
                  (ASR_STATE_HISTORY.ADATE = (SELECT MAX(ADATE) 
                                              FROM ASR_STATE_HISTORY 
                                              WHERE ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID))) AS STATE_CODE 
  FROM ASR_TASK LEFT JOIN ASR_TASK_TYPE ON ASR_TASK.TYPE_ID = ASR_TASK_TYPE.ID
                LEFT JOIN ASR_PRIORITY ON ASR_TASK.PRIORITY_ID = ASR_PRIORITY.ID
  WHERE (UNIQUE_ID = taskUniqueId) AND (ROWNUM <= 1);
END GetTaskByUniqueId;

/*
 * Description : Gets tasks by state code from database
 * Author : Lukasz Laszko
 * Params : stateCode - task state code, taken from ASR_STATE table
 * Return values: tasksCursor - reference cursor to task records 
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetTasksByState
(
    stateCode IN ASR_STATE.CODE%TYPE,
    tasksCursor OUT T_CURSOR
)
AS
BEGIN
    OPEN  tasksCursor FOR
    SELECT  ASR_TASK.ID AS ID,
            ASR_TASK.UNIQUE_ID AS UNIQUE_ID,
            ASR_TASK.NAME AS NAME,
            ASR_TASK.MEDIA_LOCATION AS MEDIA_LOCATION,
            ASR_TASK_TYPE.CODE AS TYPE_CODE,
            ASR_PRIORITY.CODE AS PRIORITY_CODE,
            (SELECT ASR_STATE.CODE 
             FROM   ASR_STATE INNER JOIN ASR_STATE_HISTORY ON ASR_STATE.ID = ASR_STATE_HISTORY.STATE_ID
             WHERE  (ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID) AND (ROWNUM = 1) AND 
                    (ASR_STATE_HISTORY.ADATE = (SELECT MAX(ADATE) 
                                                FROM ASR_STATE_HISTORY 
                                                WHERE ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID))) AS STATE_CODE 
    FROM ASR_TASK LEFT JOIN ASR_TASK_TYPE ON ASR_TASK.TYPE_ID = ASR_TASK_TYPE.ID
                  LEFT JOIN ASR_PRIORITY ON ASR_TASK.PRIORITY_ID = ASR_PRIORITY.ID
    WHERE (stateCode = (SELECT ASR_STATE.CODE 
                        FROM   ASR_STATE INNER JOIN ASR_STATE_HISTORY ON ASR_STATE.ID = ASR_STATE_HISTORY.STATE_ID
                        WHERE  (ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID) AND (ROWNUM = 1) AND 
                               (ASR_STATE_HISTORY.ADATE = (SELECT MAX(ADATE) 
                                                           FROM ASR_STATE_HISTORY 
                                                           WHERE ASR_STATE_HISTORY.TASK_ID = ASR_TASK.ID))));
END GetTasksByState;

/*
 * Description : Assigns a new state to task
 * Author : Lukasz Laszko
 * Params : taskId  - task id
 *          stateCode - task state code, taken from ASR_STATE table
 * Return values: resultCode  - >0, - all ok, new state id
 *                            - -1  - invalid task id
 *                            - -2  - invalid state code
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetTaskState
(
    taskId IN ASR_TASK.ID%TYPE,
    stateCode IN ASR_STATE.CODE%TYPE,
    resultCode OUT INT
)
AS
    taskExists INT;
    stateExists INT;    
    stateId INT;
BEGIN
    -- check if task exists
    SELECT COUNT(*) INTO taskExists FROM ASR_TASK
    WHERE (ID = taskId) AND (ROWNUM <= 1);
    IF taskExists = 0 THEN
      resultCode := -1;
      RETURN;
    END IF;

    -- check if state code exists
    SELECT COUNT(*) INTO stateExists FROM ASR_STATE
    WHERE (CODE = stateCode) AND (ROWNUM <= 1);
    IF stateExists = 0 THEN
      resultCode := -2;
      RETURN;
    ELSE
      SELECT ID INTO stateId FROM ASR_STATE
      WHERE (CODE = stateCode) AND (ROWNUM <= 1);  
    END IF;
    
    -- insert new state
    INSERT INTO ASR_STATE_HISTORY(TASK_ID, STATE_ID, ADATE)
                           VALUES(taskId, stateId, SYSTIMESTAMP)
                           RETURNING ID
                           INTO resultCode;
END SetTaskState;

/*
 * Description : Gets state history for particular task
 * Author : Lukasz Laszko
 * Params : taskId  - task id          
 * Return values: taskHistory - list of task history entries
 * History : 2008-03-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetTaskHistory
(
    taskId IN ASR_TASK.ID%TYPE,
    taskHistoryCursor OUT T_CURSOR
)
AS
BEGIN
    OPEN taskHistoryCursor FOR
    SELECT  ASR_STATE_HISTORY.ID AS ID,
            ASR_STATE_HISTORY.ADATE AS ADATE,
            ASR_STATE.CODE AS STATE_CODE
    FROM    ASR_STATE_HISTORY INNER JOIN ASR_STATE ON ASR_STATE_HISTORY.STATE_ID = ASR_STATE.ID
    WHERE   ASR_STATE_HISTORY.TASK_ID = taskId;
END GetTaskHistory;

END TASKS;
/
 
Rem No procedure found to generate DDL.
 CREATE SEQUENCE   "ASR_PRIORITY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_RESULT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1981 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_RESULT_TYPE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_STATE_HISTORY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_STATE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_TASK_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 141 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ASR_TASK_TYPE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE
/

Rem No synonym found to generate DDL.
CREATE OR REPLACE TRIGGER  "BI_ASR_PRIORITY" 
  before insert on "ASR_PRIORITY"               
  for each row  
begin   
    select "ASR_PRIORITY_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ASR_PRIORITY" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_RESULT" 
  before insert on "ASR_RESULT"               
  for each row  
begin   
    select "ASR_RESULT_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ASR_RESULT" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_RESULT_TYPE" 
 BEFORE INSERT ON ASR_RESULT_TYPE 
FOR EACH ROW 
BEGIN
  select "ASR_TASK_TYPE_SEQ".nextval into :NEW.ID from dual; 
END;


/
ALTER TRIGGER  "BI_ASR_RESULT_TYPE" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_STATE" 
 BEFORE INSERT ON ASR_STATE
FOR EACH ROW 
BEGIN
  select "ASR_STATE_SEQ".nextval into :NEW.ID from dual; 
END;


/
ALTER TRIGGER  "BI_ASR_STATE" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_STATE_HISTORY" 
  before insert on "ASR_STATE_HISTORY"               
  for each row  
begin   
    select "ASR_STATE_HISTORY_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ASR_STATE_HISTORY" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_TASK" 
  before insert on "ASR_TASK"               
  for each row  
begin   
    select "ASR_TASK_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ASR_TASK" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_ASR_TASK_TYPE" 
  before insert on "ASR_TASK_TYPE"               
  for each row  
begin   
    select "ASR_TASK_TYPE_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_ASR_TASK_TYPE" ENABLE
/

